﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Model;

namespace Data
{
    public class LimpiezaDataAccess
    {
        private DataTable dt = new DataTable();
        private SQLiteConnection con = DataBaseHelper.GetConnection("BDHostal.sqlite");        
        private DataSet dataSet = new DataSet();

        public DataTable obtenerEstado()
        {
            string cmd = "SELECT l.numeroHabitacion,total,parcial,fechaRegistro FROM limpieza l, habitacion h where numeroHabitacion=numero AND state='1'";

            try
            {
                con.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                dataSet.Reset();
                dataAdapter.Fill(dataSet);
                dt = dataSet.Tables[0];
            }
            catch (Exception ex){}
            finally
            {
                con.Close();
            }
            return dt;
        }

        public void actualizarTotal(string numeroHabitacion,string fechaRegistro)
        {
            string cmd = "UPDATE limpieza SET total='si',parcial='no',fechaRegistro='" + fechaRegistro + "' WHERE numeroHabitacion = '" + numeroHabitacion + "'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex) { }
                finally
                {
                    con.Close();
                }
            }
        }

        public void actualizarParcial(string numeroHabitacion, string fechaRegistro)
        {
            string cmd = "UPDATE limpieza SET parcial='si',total='no',fechaRegistro='" + fechaRegistro + "' WHERE numeroHabitacion = '" + numeroHabitacion + "'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex) { }
                finally
                {
                    con.Close();
                }
            }
        }

        public void actualizarSucio(string numeroHabitacion, string fechaRegistro)
        {
            string cmd = "UPDATE limpieza SET total='no',parcial='no',fechaRegistro='" + fechaRegistro + "' WHERE numeroHabitacion = '" + numeroHabitacion + "'";
            {
                try
                {
                    con.Open();
                    SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                    dataSet.Reset();
                    dataAdapter.Fill(dataSet);
                    dt = dataSet.Tables[0];
                }
                catch (SystemException ex) { }
                finally
                {
                    con.Close();
                }
            }
        }

        public void registrar(habitacion h)
        {
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "INSERT INTO limpieza(numeroHabitacion,total,parcial,fechaRegistro) VALUES ('" + h.Numero + "','no','no','"+crearFechaActual()+"')";
                    cmd.ExecuteNonQuery();

                }
                catch (SQLiteException ex)
                {
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }

        public void actualizarHabitacion(habitacion h)
        {
            if (existeHabitacion(h.Numero) && cambioNumero(h))
                throw new Exception("Numero ya asignado!");
            using (var cmd = con.CreateCommand())
            {
                try
                {
                    con.Open();
                    cmd.CommandText = "UPDATE limpieza SET numeroHabitacion='" + h.Numero + "' WHERE rowid='" + h.id + "'";
                    cmd.ExecuteNonQuery();
                }
                catch (SQLiteException ex)
                {
                    /*Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new Exception("Error Modificando Habitacion.");*/
                }
                finally
                {
                    cmd.Dispose();
                    con.Close();
                }
            }
        }

        public bool existeHabitacion(string num)
        {
            string cmd = "SELECT * FROM habitacion WHERE numero = '" + num + "'";
            bool resultado = false;

            try
            {
                con.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                dataSet.Reset();
                dataAdapter.Fill(dataSet);
                dt = dataSet.Tables[0];
                if (dt.Rows.Count > 0)
                    resultado = true;
                else
                    resultado = false;


            }
            catch (SystemException ex)
            {
                //throw ex;
            }
            finally
            {
                con.Close();
            }
            return resultado;
        }

        private bool cambioNumero(habitacion h)
        {
            string cmd = "SELECT * FROM habitacion WHERE numero='" + h.Numero + "' AND rowid = '" + h.id + "'";
            bool resultado = false;

            try
            {
                con.Open();
                SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd, con);
                dataSet.Reset();
                dataAdapter.Fill(dataSet);
                dt = dataSet.Tables[0];
                if (dt.Rows.Count > 0)
                    resultado = false;
                else
                    resultado = true;


            }
            catch (SystemException ex)
            {
                //throw ex;
            }
            finally
            {
                con.Close();
            }
            return resultado;
        }

        public string crearFechaActual()
        {
            string s;
            if (int.Parse(System.DateTime.Today.Month.ToString()) < 10)
            {
                s = System.DateTime.Today.Year.ToString() + "/0" + System.DateTime.Today.Month.ToString();
                if (int.Parse(System.DateTime.Today.Day.ToString()) < 10)
                    s += "/0" + System.DateTime.Today.Day.ToString();
                else
                    s += "/" + System.DateTime.Today.Day.ToString();
            }
            else
            {
                s = System.DateTime.Today.Year.ToString() + "/" + System.DateTime.Today.Month.ToString();
                if (int.Parse(System.DateTime.Today.Day.ToString()) < 10)
                    s += "/0" + System.DateTime.Today.Day.ToString();
                else
                    s += "/" + System.DateTime.Today.Day.ToString();
            }
            return s;
        }
    }
}
